CRUD操作

Cerate
insert into 表名(字段1，字段2) values(值1，值2)

Retrive

获取所有记录
select 字段 from 表名
select * from 表名

条件查询(where, and, or)

select 字段 from 表名 where 字段=值 //表达式

模糊匹配(like, %)
select 字段 from 表名 where 表达式 
select 字段 from 表名 where name like "m%"

限制返回条数（limit）
查询表达式 limit 数量
select 字段 from 表名 where 表达式 limit 1

过滤重复值(distinct)
对查询的结果进行过滤
select distinct 字段 from 表名 [条件语句]

排序问题
默认升序
select 字段 from 表名 order by 字段
降序：select 字段 from 表名 order by 字段 desc

获取查询结果的条数（count）
select count(*) from 表名 [条件语句]

Update(更新)
update 表名 set 字段=新值 where 表达式

Delete(删除)
delect from 表名 where 表达式

集合操作

并集（union）
子语句select 必须有相同数量的字段，和相同的数据类型
select 字段1 from 表名1 where 字段1 is not null
union
select 字段2 from 表名2 where 字段2 is not null

交集(join, inner join)
select s1.字段1 from
(select 字段1 from 表名1 where 字段1 is not null) as s1
join
(select 字段2 from 表名2 where 字段2 is not null) as s2
on s1.字段1=s2.字段2

差集（left join, right join）

A对B 的差集
select s1.name from
(select name from class_1 where name is not null) as s1
left join
(select name from class_2 where name is not null) as s2
on s1.name=s2.name
where s2.name is null  // 限定s1有， s2没有的记录

B对A的差集
select s2.name from 
(select name from class_1 where name is not null) as s1
right join
(select name from calss_2 where name is not null) as s2
on s1.name=s.name
where s1.name is null

补集
A与B的补集=A对B的差集 + B对A的差集
selec s1.name from 
(select name from calss_1 where name is not null) as s1
left join
(select name from class_2 where name is not null) as s2
on s1.name = s2.name
where s2.name is null

union

select s2.name from
(select name from class_1 where name is not null) as s1
right join
(select name from class_2 where name is not null) as s2
on s1.name = s2.name
where s1.name is null
